Advanced Techniques

Now that you're familiar with the basic techniques for data retrieval in ADO, let's tackle a few advanced topics, such as asynchronous operations and hierarchical Recordsets.

If you've worked with RDO, you might believe that you need to cope with events only when you're performing asynchronous operations. The truth is that, while events play a pivotal role in asynchronous operations, they can be useful on many other occasions. In fact, ADO fires events whether or not the operation is asynchronous. I illustrate asynchronous queries and fetch operations in the next section, but here I want to introduce a few Recordset events that you might find useful when doing synchronous operations.

The Recordset object exposes 11 events, comprising 4 pairs of Will/Complete events plus the FetchProgress, FetchComplete, and EndOfRecordset events. The Visual Basic documentation isn't very helpful, and you can learn how events really work only by writing sample code or playing with the ADO Workbench program introduced in Chapter 13. I had to do both to discover a few undocumented (or insufficiently-documented) bits of information about events. For example, I found that ADO sometimes fires more events than I expected, as I'll show in a moment. First, let's start with the basics.

ADO can fire the following pairs of Will/Complete Recordset's events:

• A WillChangeField/FieldChangeComplete pair of events when you modify the value of a field using the Field object (but not when you're using an SQL command or stored procedure).
• A WillChangeRecord/RecordChangeComplete pair of events when an operation modifies one or more records—for example, as a consequence of an Update, UpdateBatch, AddNew, or Delete method.
• A WillMove/MoveComplete pair of events when another record becomes the current record. It can be caused by a Movexxxx, Open, AddNew, or Requery method, or an assignment to the Bookmark or AbsolutePage property.
• A WillChangeRecordset/RecordsetChangeComplete pair of events when an operation affects the entire Recordset—for example, an Open, Requery, or Resync method.

While the syntax of these events differs, they have a lot in common. For example, all of them receive an adStatus parameter. On entry to a Willxxxx event, the adStatus parameter can be adStatusOK or adStatusCantDeny: In the former case, you can set it to adStatusCancel if you want to cancel the operation that causes the event. All xxxxComplete events receive the adStatus parameter and a pError parameter containing information about errors that have occurred.

The ability to cancel the operation that caused the event is especially useful when you want to validate the value of a field: Instead of spreading the validation code all over the program, you just write it in the WillChangeField event. This event receives the number of fields in the cFields parameter and an array of Field objects in the Fields parameter. The following code example shows how you can use this event to validate values being stored in fields:

Private Sub rs_WillChangeField(ByVal cFields As Long, ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) Dim fld As ADODB.Field, i As Integer ' If we can't cancel this event, there's no point in ' validating fields. If adStatus = adStatusCantCancel Then Exit Sub ' Note that we can't use a For Each loop. For i = 0 To UBound(Fields) Set fld = Fields(i) Select Case fld.Name Case "FirstName", "LastName" ' These fields can't be empty strings or Null. If (fld.Value & "") = "" Then adStatus = adStatusCancel Case "GrandTotal" ' This field must be positive. If fld.Value < 0 Then adStatus = adStatusCancel ' Add Case blocks for other fields here. End Select Next End Sub

The WillChangeField event fires also if you're assigning the same value that is already contained in the field. You can probably save ADO some time—especially on networks with a narrow bandwidth—if you catch this case and cancel the operation. Just keep in mind that the main program should be ready to deal with the error &H80040E4E: "The change was canceled during notification; no columns are changed."

It would be great if you could trap incorrect values and fix them in the WillChangeField event procedure. Unfortunately, it seems impossible to modify the value of a field within this event: You can only accept or reject the value set by the main program. This event receives multiple fields when the main program has called an Update method with a list of fields' names and values. You don't really need the cFields parameter because you can use UBound(Fields)+1 instead.

The FieldChangeComplete event has limited use, at least as far as field validation is concerned. You might use it to update values on screen if you aren't using bound controls. If you're using bound controls, you might want to use this event to update other (unbound) controls that contain calculated values. Be aware, however, that this event—and all the xxxxComplete events, for that matter—fires even if the corresponding operation was canceled by the program or because of an error raised by ADO. For this reason, you should always check the adStatus parameter first:

Private Sub rs_FieldChangeComplete(ByVal cFields As Long, _ ByVal Fields As Variant, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) If adStatus <> adStatusErrorsOccurred Then ' Update your unbound controls here. End If End Sub

If you have absolutely no use for this event, you can (slightly) improve execution speed by asking ADO not to fire it again:

Private Sub rs_FieldChangeComplete(& ) ' This event will be invoked only once. adStatus = adStatusUnwantedEvent End Sub

In general, individual field validation isn't sufficient to ensure that the database contains valid data. As a rule, you need to validate all fields just before the record is written to the database. This is the ideal job for the WillChangeRecord event.

On entry to this event, adReason holds a value that indicates why the record is being changed, and cRecords holds the number of affected records. (For a list of values that adReason can receive, see Table 13-4 in Chapter 13.) The first time you update a field in the current record, ADO fires a WillChangeRecord event (and its RecordChangeComplete companion event) with adReason set to adRsnFirstChange, to give you the opportunity to prepare for a record update (and possibly reject it). When the record is ready to be written to the database, ADO fires another WillChangeRecord/RecordChangeComplete pair of events, this time with a more specific value in adReason. You should take values in Table 13-4 with a grain of salt, however. For example, I noticed that even if the record is updated because of a MoveNext method, the WillChangeRecord event receives adReason equal to adRsnUpdate. This is the implicit Update method that ADO invokes for you when you change one or more fields and then move to another record.

Inside the WillChangeRecord event, you can't modify the value of the Recordset's fields, so you can't use this event to provide default values to fields, automatically fix invalid values, force to uppercase or lowercase, and so on. You can only test the fields' values and reject the update operation as a whole if you find some value incorrect or incomplete. Because of the extra event fired when the first field is being modified, you must always test the value of the adReason parameter:

Private Sub rs_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, _ ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset) If adReason <> adRsnFirstChange Then ' These two fields can't both be empty strings. If rs("CustAddress") = "" And rs("ShipAddress") = "" Then adStatus = adStatusCancel End If End If End Sub

If your application displays data without using bound controls, you must write the code that retrieves data from the Recordset and shows it on screen as well as code that moves data from a control to the database. Typically, you use the WillMove event to move data from controls to the database and use the MoveComplete event to move data from the database to controls. Let's start with the latter event, the code for which is shown here.

' Assumes that the form contains two TextBox controls. Private Sub rs_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, _ ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset) ' Move data from the Recordset to on-screen controls. txtFirstName.Text = rs("FirstName") txtLastName.Text = rs("LastName") ' Clear the controls' "modified" flag. txtFirstName.DataChanged = False txtLastName.DataChanged = False End Sub

As you see in the preceding code snippet, you can use the DataChanged property even in unbound controls. In fact, this property is perfectly functional with regular controls in that Visual Basic automatically sets it to True when the contents of the control changes. The only substantial difference is in how the DataChanged property is reset: When you're using bound controls, Visual Basic resets this property to False automatically, but when you're using unbound controls you must do that manually. You can then test the value of the DataChanged property in the WillMove event to understand whether you really need to move values from the on-screen controls to the database:

Private Sub rs_WillMove(ByVal adReason As ADODB.EventReasonEnum, _ adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) ' Move data to Recordset only if user modified the controls' contents. If txtFirstName.DataChanged Then rs("FirstName") = txtFirstName.Text If txtLastName.DataChanged Then rs("LastName") = txtLastName.Text End Sub

In a more robust implementation of this concept, you should test the adReason parameter and react accordingly. For example, you can decide whether you should save values to the database when the Recordset is being closed. Or you can load default values in controls when the MoveComplete event has fired because of an AddNew method. Unlike WillChangeField and WillChangeRecord events, the WillMove event does permit you to assign values to the Recordset's fields, so you can use this event to provide default values or calculated fields:

' In the WillMove event If txtCountry.Text = "" Then rs("country") = "USA"

One single operation on the Recordset fires a lot of (nested) events. For example, the following table shows which events fire because of a simple sequence of methods:

Method	Events
rs.Open	WillExecute WillMove (adReason = adRsnMove) MoveComplete (adReason = adRsnMove) ExecuteComplete
rs("FirstName") = "John"	WillChangeRecordset (adReason = adRsnMove) RecordsetChangeComplete (adReason = adRsnMove) WillMove (adReason = adRsnMove) MoveComplete (adReason = adRsnMove) WillChangeRecord (adReason = adRsnFirstChange) WillChangeField FieldChangeComplete RecordChangeComplete (adReason = adRsnFirstChange)
rs("LastName") = "Smith"	WillChangeField ChangeFieldComplete
rs.MoveNext	WillMove (adReason = adRsnMoveNext) WillChangeRecord (adReason = adRsnUpdate) RecordChangeComplete (adReason = adRsnUpdate) WillChangeRecordset (adReason = adRsnMove) RecordsetChangeComplete (adReason = adRsnMove) MoveComplete (adReason = adRsnMoveNext)

For the most part, the preceding sequence is clear and reasonable. It offers, however, a few surprises. For example, the MoveNext method fires a WillChangeRecordset/RecordsetChangeComplete pair of events. This shouldn't happen, according to the Visual Basic documentation. There's some evidence that this extra pair of events has to do with ADO filling the local cache. In fact, if you set CacheSize to a value greater than 1—say 4—these events are fired every four MoveNext operations. In other words, each time ADO refills the local cache it rebuilds the Recordset object. Store this information somewhere in your long-term memory—someday it might prove useful.

Other events can't be explained so easily. For example, why does the assignment to the FirstName field fire an extra WillMove/MoveComplete pair of events? After all, the first record is already the current record, isn't it? Honestly, I can't answer this one. Just pay attention to what code you write inside WillMove and MoveComplete events because it might execute more often than you expect.

See what happens to the previous sequence if you cancel one event. For example, if you set adStatus to adStatusCancel in the WillMove event that immediately follows the MoveNext method, all the other events are suppressed and ADO fires only the matching MoveComplete event. On the other hand, if you cancel the command in the WillChangeRecord event, ADO suppresses only the WillChangeRecordset/ RecordsetChangeComplete pair of events. In general, after you set adStatus to adStatusCancel, this value goes unmodified through all the subsequent events, until the error is returned to the main program.

ADO offers several types of asynchronous operations, all of which help to make your application more responsive to the user. I already showed that you can set up an asynchronous connection, so it's time to see how you can execute a lengthy command without having your code wait until ADO completes it.

The simplest form of asynchronous operation is a command performed through the Connection object. In this case, all you have to do is pass the adAsyncExecute value to the Options argument of the Connection's Execute method, as in the following example:

Dim cn As New ADODB.Connection, recs As Long cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=E:\Microsoft Visual Studio\VB98\Biblio.mdb" cn.Execute "DELETE FROM Publishers WHERE State = 'WA'", _ recs, adAsyncExecute Debug.Print recs & " records affected" ' Displays _1.

When you run a command in this way, ADO fires a WillExecute event before returning the control to the statement that follows the Execute statement. Because the command hasn't completed yet, the recs variable receives the special value -1. This is the syntax of the WillExecute event:

Private Sub cn_WillExecute(Source As String, _ CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, _ Options As Long, adStatus As ADODB.EventStatusEnum, _ ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _ ByVal pConnection As ADODB.Connection) MsgBox "About to execute command " & Source End Sub

Because all arguments are passed by reference, you can modify them if it makes sense to do so. You can also cancel the command, by setting the adStatus parameter to the value adStatusCancel, unless it's already set to the value adStatusCantDeny:

' Put this code inside the WillExecute event. If adStatus <> adStatusCantDeny Then If MsgBox("About to execute statement " & Source & vbCr & "Confirm?", _ vbYesNo + vbInformation) = vbNo Then adStatus = adStatusCancel End If End If

When the ADO completes the command, an ExecuteComplete event fires, with the actual number of affected records in its first parameter:

Private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, _ ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _ ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _ ByVal pConnection As ADODB.Connection) If adStatus = adStatusOK Then MsgBox "Execution of the command has been completed" & vbCr _ & RecordsAffected & " record(s) were affected", vbInformation ElseIf adStatus = adStatusErrorsOccurred Then MsgBox "Execution error: " & pError.Description, vbCritical End If End Sub

In the WillExecute event, you can determine whether you're executing a row-returning command by checking the value in CursorType or LockType: If either contains -1, this is an action command. When the ExecuteComplete event fires because of a Recordset's Open statement, you find a reference to the Recordset object in pRecordset, which isn't very exciting because you already have a reference to the Recordset being opened. The pRecordset parameter is more useful when you complete a row-returning Execute command of a Connection's object because it contains the results of the query. So, for instance, you can assign it to an ADO Data control or process it in any way you prefer.

As you would expect, the pCommand parameter in the WillExecute event contains a reference to a Command object if the event has been fired because of a Command's Execute method; otherwise, the parameter contains Nothing. Interestingly, even if you aren't using a Command object, ADO manufactures a temporary Command object to perform the query and passes a reference to this temporary object in the pCommand parameter of the ExecuteComplete event. This temporary object lets you retrieve information such as the Source string, which isn't otherwise available after the query has completed:

' In the ExecuteComplete event ' The next statement works with *any* type of command or query. Debug.Print "Statement " & pCommand.CommandText & " has been completed"

A more interesting (and advanced) use for this capability is repeating a command or a query that failed, for example, because of a timeout. In a situation like this, you simply run the Command object's Execute method and pay some attention to reentrancy problems.

ADO gives you an additional degree of control over asynchronous queries with the adAsyncFetch value. You can pass this value to a Connection's Execute method and to a Recordset's Open or Requery methods. While the adAsyncExecute value tells ADO that the query should be performed asynchronously, the adAsyncFetch value informs ADO that it should fetch data from the data source to the Recordset in asynchronous mode. Accordingly, ADO executes the query and immediately fills the local cache with the first group of result records and then fetches all remaining records asynchronously.

If the fetch operation takes some time, ADO fires a FetchProgress event, which you can use to display a progress bar to your end users. When the fetch is complete, ADO fires a FetchComplete event. For more information about the adAsyncFetch and adAsychFetchNonBlocking options, see the description of the Command's Execute method in Chapter 13.

Client/server applications based on SQL Server or Oracle implement much of their functionality using stored procedures. A stored procedure is a procedure written in the SQL dialect of the hosting database and is compiled to improve execution speed. Stored procedures let the developer enforce better security while improving performance, just to mention a couple of outstanding advantages. As you'll see in a moment, both ADO and Visual Basic 6 Enterprise Edition have a lot to offer when you're working with stored procedures.

If you open the DataView window and select a data link to an SQL Server or Oracle database, you'll find a subfolder named Stored Procedures, inside of which is the list of all the stored procedures available for that database. You can open the node corresponding to a stored procedure to see its return value and arguments (if any), and you can double-click on an argument node to see its properties. The property window of a parameter displays the ADO data type for that parameter, which is vital information when you have to create the Parameters collection of the Command object that runs this stored procedure.

Double-click on the name of a stored procedure to bring up the SQL Editor, which lets you edit a stored procedure without leaving the Visual Basic IDE. You can use this editor to create triggers as well. Curiously, there's a minor bug in this feature's implementation: When you display the SQL Editor, the Data View window's font is changed to match the font in the editor, as you can see in Figure 14-3. It's a rather harmless bug, and I even found a use for it: When I'm teaching a class and someone complains that the DataView window is hardly readable, I bring up the Stored Procedure Editor and immediately close it, just to switch to a larger font.

As if the integrated editor weren't enough, if you're working with SQL Server (but not with Oracle) you can debug your stored procedures right in the Visual Basic environment. This works even with remote servers and uses OLE Remote Automation to physically connect to the database. You can also use the T-SQL Debugger add-in to execute system or batch stored procedures. The T-SQL Debugger lets you set breakpoints, step in and out of nested procedures, watch local and global variables, display the call stack, and so on. When you're developing a complex application, this feature alone can save you dozens of hours.

Setting up the TSQL Debugger isn't intuitive, so here are a few tips that should prove useful. First, the debugger works only with SQL Server 6.5 Service Pack 3 or later. (Visual Basic 6 comes with SQL Server 6.5 Service Pack 4.) Second, you must tick the SQL Server Debugging option when you're installing BackOffice immediately after you've installed Visual Basic 6 Enterprise Edition. Third, the SQL Server service should be configured to log in as a user with sufficient permissions; logging in as a Windows NT system account won't work. Finally, ensure that OLE Remote Automation is working and correctly configured on your machine.

You can invoke the editor from the SQL Editor or from the Add-Ins menu if you have installed and activated the T-SQL Debugger add-in. In the latter instance, you have to specify a DSN and the database you're logging in to, as shown in Figure 14-4, but you can also debug batch stored procedures. If you want to debug stored procedures and triggers when they're invoked by your code, select the T-SQL Debugging Options command in the Tools menu and tick the Automatically Step Into Stored Procedures Through RDO And ADO Connections option. (See Figure 14-5.)

Figure 14-3. Visual Basic 6 Enterprise Edition lets you edit SQL Server stored procedures and even debug them.

Figure 14-4. The T-SQL Debugger add-in.

Figure 14-5. The T-SQL Debugger Options dialog box. Notice that the timeout value is in milliseconds.

Parameterized stored procedures

I've already shown you how you can use ADO Command objects to run parameterized SQL queries and how you can build their Parameters collection or let ADO build them for you. Working with parameterized stored procedures isn't much different, provided that you're alert to some quirks.

You can let ADO automatically build the Command's Parameters collection. You do this by simply referencing the Parameters collection in code or by issuing an explicit Parameters.Refresh command. This solution has many benefits, including fewer errors in your code because ADO correctly retrieves the names and the types of all the parameters and automatically accounts for the return value by creating a Parameter object whose name is RETURN_VALUE. A particular advantage of this solution is that if you later modify the type of a parameter, you don't have to change your Visual Basic code. Unfortunately, ADO needs a trip to the server to retrieve information about the stored procedure. This extra command is performed only the first time you reference the Parameters collection, however, so in most cases this overhead is negligible as long as you keep the Command object alive for the entire session. A potential problem is that ADO might be confounded by output parameters and mistakenly believe that they're input/output parameters. If this happens, you can simply set the parameter's Direction property to a correct value. Conveniently, this property is read/write even after the parameter has been added to the collection.

If you want to save ADO a trip to the server, you can build the Parameters collection yourself. The following code example invokes the byroyalty stored procedure that comes with the sample SQL Server Pubs database:

Dim cn As New ADODB.Connection, cmd As New ADODB.Command Dim rs As ADODB.Recordset ' Establish the connection. cn.Open "Provider=sqloledb;Data source=p2;user id=sa;initial catalog=pubs" Set cmd.ActiveConnection = cn ' Define the stored procedure. cmd.CommandText = "byroyalty" cmd.CommandType = adCmdStoredProc ' Save ADO some work by creating the parameter yourself. cmd.Parameters.Append cmd.CreateParameter("@percentage", adInteger, _ adParamInput) ' Set a value to this parameter, and execute the query. cmd.Parameters("@percentage") = 100 Set rs = cmd.Execute()

When you're manually building the Parameters collection, you must pay attention to an important detail: If the stored procedure returns a value, it must be the first parameter. To see how you can work with return values and output parameters, double-click on the byroyalty stored procedure in the DataView window to bring up the SQL Editor and modify the text of the procedure as follows. (Added or modified code is in boldface.)

CREATE PROCEDURE byroyalty2 @percentage int, @totalrecs Int Output AS select @totalrecs= count(*) from titleauthor select au_id from titleauthor where titleauthor.royaltyper = @percentage return (@@rowcount)

Here's the Visual Basic code that prepares the Parameters collection, runs the query, and prints the results:

cmd.CommandText = "byroyalty2" cmd.CommandType = adCmdStoredProc ' Create the Parameters collection With cmd.Parameters .Append cmd.CreateParameter("RETVAL", adInteger, adParamReturnValue) .Append cmd.CreateParameter("@percentage", adInteger, adParamInput) .Append cmd.CreateParameter("@totalrecs", adInteger, adParamOutput) End With ' Set a value for input parameters, and run the stored procedure. cmd.Parameters("@percentage") = 100 Set rs = cmd.Execute() ' Dump the contents of the recordset. Do Until rs.EOF Print "Au_ID=" & rs("au_id") rs.MoveNext Loop rs.Close ' Print the values of the output parameter and the return value. Print "Records in titleauthor = " & cmd.Parameters("@totalrecs") Print "Records returned by the query = " & cmd.Parameters("RETVAL")

Here are a couple of points worth noting. First, you can use any name for the return value parameter as long as it's the first item in the collection. Second, and most important, you must close the Recordset (or set it to Nothing to have it closed by ADO) before accessing return values and output parameters. This holds true for forward-only, read-only Recordsets returned by SQL Server and can possibly apply to other cursor types and providers. According to official documentation, ADO reads output parameters and the return value only once from the provider, so if you try to read them before they're available you won't have a second chance.

The next great feature of ADO is its ability to work with multiple result sets. In Chapter 13, I explained how you can use the NextRecordset method, but here I'll show you practical examples. This is the Visual Basic code that you can use to explore multiple result sets:

' This code assumes that all properties have been correctly initialized. Set rs = cmd.Execute() Do Until rs Is Nothing If rs.State = adStateClosed Then Print "---- Closed Recordset Else Do Until rs.EOF For Each fld In rs.Fields Print fld.Name & "="; fld & ", "; Next Print rs.MoveNext Loop Print "---- End of Recordset" End If Set rs = rs.NextRecordset Loop

To see how SQL Server and ADO deal with a stored procedure, right-click on the Stored Procedures folder in the DataView window, select the New Stored Procedure menu command, and then enter the following code in the SQL Editor:

Create Procedure PubsByCountry As Select pub_name From Publishers where country='USA' Select pub_name From Publishers where country='France' Select pub_name From Publishers where country='Germany' Select pub_name From Publishers where country='Italy'

When you run the PubsByCountry stored procedure using the Visual Basic code that I showed you previously, you get the result shown below.

pub_name=New Moon Books pub_name=Binnet & Hardley pub_name=Algodata Infosystems pub_name=Five Lakes Publishing pub_name=Ramona Publishers pub_name=Scootney Books ---- End of Recordset pub_name=Lucerne Publishing ---- End of Recordset pub_name=GGG&G ---- End of Recordset ---- End of Recordset

The last SELECT statement returns a Recordset object that doesn't contain any records. If you then execute the NextRecordset method one more time, you get Nothing and the loop exits. Let's see another example of a query that returns multiple Recordsets. This is the source code of the reptq1 stored procedure that comes with the Pubs sample database:

CREATE PROCEDURE reptq1 AS select pub_id, title_id, price, pubdate from titles where price is NOT NULL order by pub_id COMPUTE avg(price) BY pub_id COMPUTE avg(price)

This is the output that the previous routine produces when you execute the reptq1 stored procedure. As you see, the first COMPUTE statement generates a separate Recordset for each publisher, whereas the second COMPUTE statement generates a final Recordset with the average price for all the publishers:

pub_id=0736, title_id=BU2075, price=2.99, pubdate=6/30/91, pub_id=0736, title_id=PS2091, price=10.95, pubdate=6/15/91, pub_id=0736, title_id=PS2106, price=7, pubdate=10/5/91, pub_id=0736, title_id=PS3333, price=19.99, pubdate=6/12/91, pub_id=0736, title_id=PS7777, price=7.99, pubdate=6/12/91, ---- End of Recordset avg=9.784, ---- End of Recordset pub_id=0877, title_id=MC2222, price=19.99, pubdate=6/9/91, pub_id=0877, title_id=MC3021, price=2.99, pubdate=6/18/91, pub_id=0877, title_id=PS1372, price=21.59, pubdate=10/21/91, pub_id=0877, title_id=TC3218, price=20.95, pubdate=10/21/91, pub_id=0877, title_id=TC4203, price=11.95, pubdate=6/12/91, pub_id=0877, title_id=TC7777, price=14.99, pubdate=6/12/91, ---- End of Recordset avg=15.41, ---- End of Recordset pub_id=1389, title_id=BU1032, price=19.99, pubdate=6/12/91, pub_id=1389, title_id=BU1111, price=11.95, pubdate=6/9/91, pub_id=1389, title_id=BU7832, price=19.99, pubdate=6/22/91, pub_id=1389, title_id=PC1035, price=22.95, pubdate=6/30/91, pub_id=1389, title_id=PC8888, price=20, pubdate=6/12/94, ---- End of Recordset avg=18.976, ---- End of Recordset avg=14.7662, ---- End of Recordset

In theory, you might retrieve multiple result sets and assign them to different Recordset variables, or at least the syntax of the NextRecordset method seems to make it possible. Unfortunately, as of this writing no OLE DB provider supports this capability, so you're forced to retrieve and process one Recordset at a time. Or you can use the Clone method (if the Recordset is capable of being cloned) to retrieve all Recordsets, assign them to items of an array, and process them later:

Dim cn As New ADODB.Connection, rs As ADODB.Recordset ' We can reasonably assume that 100 Recordset items will suffice. Dim recs(100) As ADODB.Recordset, recCount As Integer ' Open the connection, and retrieve the first Recordset. cn.Open "Provider=sqloledb;Data source=p2;user id=sa;" _ & "initial catalog=pubs" Set rs = New ADODB.Recordset rs.Open "PubsByCountry", cn ' Retrieve all Recordsets, and clone them. Do recCount = recCount + 1 Set recs(recCount) = rs.Clone Set rs = rs.NextRecordset Loop Until rs Is Nothing ' Now the recs() array contains one clone for each Recordset.

Unfortunately, it seems impossible to use this technique to update fields in the database: Any attempt to send data back to SQL Server through the Recordsets now stored in the recs() array raises an error &H80004005, "Insufficient base table information for updating or refreshing." You can't even disconnect the Recordset and close the connection because all the Recordsets in the array are immediately closed, even if the original Recordset was configured to use optimistic batch updates. In short, you can store cloned Recordsets in an array, but in practice this is only useful when you want to process their contents at the same time (for example, when you want to compare the records in them). Here are a few additional tips concerning multiple result sets:

• Multiple statements that aren't row-returning commands generate closed Recordsets. You should test for this using the State property.
• All the queries following the first one are executed when you invoke the NextRecordset method; if you close the Recordset before retrieving all the pending result sets, the corresponding queries will never execute.
• If you specify the adAsyncFetch option, only the first Recordset is retrieved asynchronously; all the subsequent ones are fetched synchronously.
• All the Recordsets created by the NextRecordset command use the same cursor type and location as the original one. Most of the time, you'll need a client-side cursor to process multiple SQL statements or server-side cursorless Recordsets.
• Don't use an auto-instancing variable for the Recordset object, otherwise the Is Nothing test will never be successful.

If I were asked to choose the feature of ADO that has impressed me most, I would undoubtedly pick the ability to create hierarchical Recordset objects. Hierarchical Recordsets can contain child Recordset objects, much like a folder can contain other folders. For example, you can create a Recordset from the Publishers table, where each record contains data about an individual publisher plus a child Recordset that contains the list of titles published by that company. Each record in this child Recordset can contain information about each title, plus another child Recordset that contains data about that book's authors, and so on. You can nest hierarchical Recordset objects without any limit—theoretically, anyway—to the number of nesting levels. Creating hierarchical Recordsets is also known as data shaping.

You can build hierarchical Recordsets in two distinct ways. The easiest method is to interactively build a Command object at design time using the DataEnvironment designer, as I showed you in Chapter 8. The more difficult technique—which is also the more flexible one—is to create the hierarchical Recordset through code at run time.

The first thing to do when creating a hierarchical Recordset is to select the right provider. You need a specific provider designed for doing data shaping. This provider will in turn connect to the OLE DB provider that actually accesses the data source. Currently, the only provider that offers data shaping capabilities is the MSDataShape provider, but in theory any vendor might create another provider of this type in the future. When you're working with the MSDataShape provider, you specify the actual data source using the Data Provider argument in the connection string:

Dim cn As New ADODB.Connection cn.Open "Provider=MSDataShape.1;Data Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source= " & DBPATH

The MSDataShape provider supports two commands, the SHAPE APPEND keyword and the SHAPE COMPUTE keyword. The SHAPE APPEND keyword lets you create the relationship between two SQL commands that return records. Its syntax is the following:

SHAPE {parent_command} [[AS] table-alias] APPEND {child_command} [[AS] table-alias] RELATE(parent_column TO child_column) [[AS] table-alias]

where parent_command is the SQL command that returns the main Recordset, and child_command is the SQL command that returns the child Recordset. The two commands must have one column in common (although the column can have a different name in each table), and you specify that name or names in the RELATE clause. The following is a simple SHAPE APPEND command, which returns a hierarchical Recordset containing all Publishers and a child Recordset that lists all the titles by the current publisher:

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.Open "Provider=MSDataShape.1;Data Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Microsoft Visual Studio\vb98\biblio.mdb" Set rs.ActiveConnection = cn rs.Open "SHAPE {SELECT * FROM Publishers} " _ & "APPEND ({SELECT * FROM Titles} " _ & "RELATE PubID TO PubID) AS Titles"

The name used in the AS clause after the RELATE clause becomes the name of the field that contains the child Recordset. To display the contents of a hierarchical Recordset, you can assign it to the DataSource property of a Hierarchical FlexGrid control, as in this line of code:

Set MSHFlexGrid1.DataSource = rs

You can nest multiple SHAPE APPEND commands to set a relationship between multiple pairs of commands. For example, the following code snippet is the command that builds a three-level hierarchical Recordset to contain all the authors in Biblio.mdb. Each record contains a field named Title_Author, which contains a child Recordset with one record for each title written by that author. In turn, this Recordset has a child Recordset containing one single record: the record from the Titles table that corresponds to a particular title. I have indented the SHAPE APPEND commands to make their relationship as clear as possible:

SHAPE {SELECT * FROM Authors} AS [Authors With Titles] APPEND (( SHAPE {SELECT * FROM [Title Author]} APPEND ({SELECT * FROM Titles} RELATE ISBN TO ISBN) AS Titles1) RELATE Au_ID TO Au_ID) AS Title_Author

The name after the first AS clause—Authors With Titles, in this example—is the name of the hierarchical command created, and usually it can be omitted when you pass the string to the Open method of a Recordset object or to the CommandText property of a Command object. The fields listed in the RELATE clause can have different names as long as they refer to the same information. If you don't provide a name after the parenthesis that closes the RELATE clause, the default field name chapter is used.

A hierarchical Recordset can have more than one child Recordset. For example, the following SHAPE APPEND command is similar to the previous one but adds another child Recordset that lists all the authors that are born in the same year as the author pointed to by the parent Recordset. Notice that the APPEND keyword isn't repeated and subsequent child commands at the same nesting level are separated by a comma:

SHAPE {SELECT * FROM Authors} APPEND (( SHAPE {SELECT * FROM [Title Author]} APPEND ({SELECT * FROM Titles} RELATE ISBN TO ISBN) AS Titles1) AS Title_Author RELATE Au_ID TO Au_ID) AS Title_Author, ({SELECT * FROM Authors} RELATE [Year Born] TO [Year Born]) AS AuthorsBornSameYear

While the SHAPE APPEND command creates a child Recordset starting from the parent (main) Recordset, the SHAPE COMPUTE command works the other way around: It executes an aggregate function on the rows of a Recordset to create a parent Recordset. For example, you can start with a Recordset that contains the records in the Titles table and build a parent Recordset where the titles are grouped by their Year Published field. In this instance, the parent Recordset has two fields: the first is Year Published, and the second is a Recordset that contains all the titles published in that year. The syntax of the SHAPE COMPUTE command is as follows:

SHAPE {child_command} [[AS] table_alias] COMPUTE aggregate_command_field_list [BY grp_field_list]

where child_command is the Recordset you start with and typically is a SELECT statement that returns a group of records; table_alias is the name of the field in the parent Recordset that will contain the child Recordset; aggregate_command_field_list is the list of fields on which the aggregate function operates; and grp_field_list is the list of fields the child Recordset is grouped by.

In the simplest situation, you group records in the child Recordset according to the value of one field. For example, you can group Titles by the Year Published field using this command:

' You can enclose field and table names within ' single quotes or square brackets. rs.Open "SHAPE {SELECT * FROM Titles} AS Titles " _ & "COMPUTE Titles BY 'Year Published'"

The name following the COMPUTE keyword must coincide with the alias name assigned to the child Recordset. You can group by multiple fields, using the comma as a separator after the BY keyword:

' Group titles by publishers and year of publication. rs.Open "SHAPE {SELECT * FROM Titles} AS Titles " _ & "COMPUTE Titles BY PubID, 'Year Published'"

The COMPUTE command can be followed by a list of fields or functions among those listed in Table 14-1. Typically you append an AS clause to indicate the name of the aggregate field in the parent Recordset:

' Group titles by publishers, and add a field named TitlesCount that ' holds the number of titles by each publisher. rs.Open "SHAPE {SELECT * FROM Titles} AS Titles " _ & "COMPUTE Titles, COUNT(Titles.Title) AS TitlesCount BY PubID"

Table 14-1. Functions supported by SHAPE COMPUTE. Alias is the name of the child Recordset as it appears in the command.

Function Syntax	Action/Returned Value
COUNT(alias[.fieldname])	The number of rows in the child Recordset
SUM(alias.fieldname)	The sum of all values in the specified field
MIN(alias.fieldname)	The minimum value in the specified field
MAX(alias.fieldname)	The maximum value in the specified field
AVG(alias.fieldname)	The average of all values in the specified field
STDEV(alias.fieldname)	The standard deviation of all the values in the specified field
ANY(alias.fieldname)	The value of a column (where the value of the column is the same for all rows in the child Recordset)
CALC(expression)	The result of an expression that uses values from the current row only
NEW(fieldtype, [width | scale [, precision])]	Adds an empty column of the specified type to the Recordset

You can use the CALC function to evaluate an arbitrary expression that contains fields from the current row in the parent Recordset. For example, you can group titles by publisher and also add three fields with the year a publisher began to publish books, the year it published its most recent book, and the difference between these values:

rs.Open " SHAPE {SELECT * FROM Titles} AS Titles2 " _ & "COMPUTE Titles2, MIN(Titles2.[Year Published]) AS YearMin, " _ & "MAX(Titles2.[Year Published]) AS YearMax, " _ & "CALC(YearMax - YearMin) AS YearDiff BY PubID"

Hierarchical Recordsets can be browsed in much the same way as regular Recordsets. The only difference is in the way you deal with Field objects that contain child Recordsets. To retrieve data in those Recordsets, you must first assign the Field's Value property to a Recordset variable, as the following code demonstrates:

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset Dim rsTitles As ADODB.Recordset cn.Open "Provider=MSDataShape.1;Data Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & DBPATH Set rs.ActiveConnection = cn rs.Open "SHAPE {SELECT * FROM Titles} AS Titles " _ & "COMPUTE Titles, COUNT(Titles.Title) AS TitlesCount BY PubID" ' Have the rsTitles variable always point to the child Recordset. ' (The StayInSync property's default value is True.) Set rsTitles = rs("Titles").Value ' Browse the parent Recordset. Do Until rs.EOF ' Show information in summary fields. Debug.Print "PubID=" & rs("PubID") Debug.Print "TitlesCount=" & rs("TitlesCount") ' For each row in the parent, browse the child recordset. Do Until rsTitles.EOF Debug.Print " " & rsTitles("Title") rsTitles.MoveNext Loop rs.MoveNext Loop

If the parent Recordset is updatable, you can use standard ADO commands to update values in the child Recordset as well. You can distinguish Fields that contain a child Recordset from regular Fields because their Type property returns the value 136-adChapter.

The parent Recordset's StayInSync property affects how object variables pointing to child Recordsets are updated when the record pointer in the main Recordset moves to another record. The default value for this property is True, which means that once you have assigned the Field object to a Recordset variable (rsTitle, in the previous code example), this variable correctly points to child records even when the parent Recordset moves to another row. This setting simplifies the navigation in the hierarchical Recordset and slightly optimizes execution speed because you don't have to reexecute the Set command after each Movexxxx command. Under certain circumstances, you might want to set StayInSync to False, which detaches the object variable from the parent Recordset.

The real advantage in building hierarchical Recordsets in code instead of using DataEnvironment's Command objects defined at design time is the greater flexibility you have when building complex SHAPE commands. For example, you can add WHERE clauses in the nested SELECT commands, as in the following snippet:

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, Dim cmd As New ADODB.Command, source As String cn.Open "Provider=MSDataShape.1;Data Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Microsoft Visual Studio\Vb98\biblio.mdb" source = "SHAPE {SELECT * FROM Titles WHERE [Year Published] = 1990} " _ & "AS Titles COMPUTE Titles BY PubID" Set cmd.ActiveConnection = cn cmd.CommandText = source Set rs = cmd.Execute()

I didn't manage to have the Command object work with ? parameters embedded in SHAPE commands, so it seems that you have to forgo parameterized queries when you're working with the MSDataShape provider. This isn't as bad as it might sound at first, though, because hierarchical Recordsets are inherently client-side and are never compiled on the server. You can create pseudoparameterized SHAPE commands using placeholders and the ReplaceParams routine I introduced earlier in this chapter:

source = "SHAPE {SELECT * FROM Titles WHERE [Year Published] = @1} " _ & "AS Titles COMPUTE Titles BY PubID" cmd.CommandText = ReplaceParams(source, "1990") Set rs = cmd.Execute()

You can also decide at run time the names of chapter fields and the expression in the WHERE condition, which is impossible when using design-time DataEnvironment objects.

When you use hierarchical Recordsets, ADO downloads all the data from the main table and the child table and builds the relationship on the client workstation. Needless to say, when you're working with large tables—as all real applications do—this adds considerable overhead, both in terms of network traffic and resources on the client workstation. You can reduce this overhead by using the special parameterized syntax shown on the shown below for the Source property or argument of a hierarchical Recordset.

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.Open "Provider=MSDataShape.1;Data Provider=sqloledb.1;" _ & "Data Source=p2;user id=sa;initial catalog=pubs" Set rs.ActiveConnection = cn rs.Open "SHAPE {SELECT * FROM Publishers} " _ & "APPEND ({SELECT * FROM Titles WHERE pub_id = ?} " _ & "RELATE pub_id TO PARAMETER 0) AS Titles"

When you use this syntax, ADO doesn't download the entire Titles table. Instead, it downloads only the Publisher table (unless you add a suitable WHERE clause to the first SELECT, of course). Then it uses the value of the Pub_Id key field to retrieve only the items in Titles that correspond to that value. Each time you move to another record in Publisher, ADO issues another SELECT against the Titles table, so only a fraction of this table is downloaded each time.

This technique is extremely efficient also because ADO automatically builds a temporary stored procedure on the server to retrieve chunks of the child table. But the overall execution time is higher than with the standard technique because of the multiple queries, so there's no point in using these parameterized commands when you're going to assign the result Recordset to a Hierarchical FlexGrid control. The parameterized command might be more convenient, though, because ADO optimizes the access to the child Recordset and retrieves its records only if the application actually references one of its properties and events, as the following code demonstrates:

' Continuing the previous code example... ' Print the number of titles for US publishers. (This is just an example: ' in a real program you should add a WHERE clause to the Open method.) Dim rs2 As ADODB.Recordset Set rs2 = rs("titles").Value ' Make the assignment just once. Do Until rs.EOF If rs("country") = "USA" Then ' The next statement actually retrieves the records from Titles. Print rs("pub_name"), rs2.RecordCount End If rs.MoveNext Loop

Fields that are retrieved only when referenced in code are called deferred fields. Even if you're going to process all the records in the child Recordset, using a parameterized command can help if the client workstation is short on system memory.

If you've defined a hierarchical Command object at design time in a DataEnvironment designer, using it from code is really straightforward: You only have to retrieve it using the Commands collection and assign it to a regularADODB.Command object variable, as in this code:

Dim cmd As ADODB.Command, rs As ADODB.Recordset Set cmd = DataEnvironment1.Commands("Authors") Set rs = cmd.Execute Set MSHFlexGrid1.DataSource = rs

Or you can execute the query and then retrieve the result Recordset using custom methods of the DataEnvironment object:

DataEnvironment1.Authors Set MSHFlexGrid1.DataSource = DataEnvironment1.rsAuthors

Even if you don't plan to use DataEnvironment objects in your code, the DataEnvironment designer is helpful for building SHAPE commands. In fact, you can interactively design a hierarchical Command object, and then right-click on it and select the Hierarchy Information menu command, as shown in Figure 14-6.

Figure 14-6. Let the DataEnvironment designer build complex SHAPE commands for you.

This chapter and Chapter 13 together offer an in-depth description of how ADO works and how you can work around its few limitations and quirks. All the code seen so far, however, has focused on data retrieval exclusively. In the next chapter, I show you how you can use bound grid controls and the DataReport designer to display the result of queries to your users.